import sys
import pandas as pd
import re

LOG_FILE = 'pretask/pretask.txt'
log_file = open(LOG_FILE, 'w', encoding='utf-8')
original_stdout = sys.stdout  # 保存原始标准输出
sys.stdout = log_file


def normalize_id(val, length=2):
    if pd.isna(val): 
        return val
    num_str = re.sub(r"\D", "", str(val))  # 移除非数字字符
    if num_str: 
        return num_str.zfill(length)  # 补零至固定长度
    return str(val)  # 处理纯非数字值

# 处理 df1
df1 = pd.read_excel("附件.xlsx", sheet_name="表单1")
df1["文物编号"] = df1["文物编号"].apply(lambda x: normalize_id(x, length=2))

# 处理 df2
df2 = pd.read_excel("附件.xlsx", sheet_name="表单2")
df2["文物编号"] = df2["文物采样点"].apply(
    lambda x: normalize_id(re.search(r"\d+", str(x)).group(), length=2) 
    if re.search(r"\d+", str(x)) else None
)
# 选取df1中编号为01-09的行
df1_sub = df1[df1["文物编号"].isin(['01','02','03','04','05','06','07','08','09'])]
print(df1_sub)


df2_sub = df2[df2["文物编号"].isin(['01','02','03','04','05','06','07','08','09'])]
print(df2_sub)

# for i in range(min(5, len(df2_sub))):
#     item = df2_sub["文物编号"].iloc[i]
#     print(f"行{i}: 值类型={type(item)}, 值={item!r}, 字符串表示={str(item)}")

# 如果df1_sub为空，说明df1中并没有01-09行的数据；如果有数据，则检查这些行
# print(df1["文物编号"].unique())  # 应输出 ['01','02',...,'09','10',...]
# print(df2["文物编号"].unique())  # 格式应与 df1 一致

# 合并表格
merged_df = pd.merge(df2, df1, on="文物编号", how="left")


composition_cols = [
    "二氧化硅(SiO2)", "氧化钠(Na2O)", "氧化钾(K2O)", "氧化钙(CaO)",
    "氧化镁(MgO)", "氧化铝(Al2O3)", "氧化铁(Fe2O3)",
    "氧化铜(CuO)", "氧化铅(PbO)", "氧化钡(BaO)", "五氧化二磷(P2O5)",
    "氧化锶(SrO)", "氧化锡(SnO2)", "二氧化硫(SO2)"
]

# print(merged_df)

# 以表二采样点风化点为准
def determine_weathering(row):
    sample = row["文物采样点"]
    if "严重风化" in sample:
        return "风化"
    elif "未风化" in sample:
        return "无风化"
    else:
        return row["表面风化"]

merged_df["表面风化"] = merged_df.apply(determine_weathering, axis=1)

# 计算成分总和（跳过 NaN）
merged_df["成分总和"] = merged_df[composition_cols].apply(
    lambda row: sum(val for val in row if pd.notna(val)), axis=1
)

# 成分列补零处理
merged_df[composition_cols] = merged_df[composition_cols].fillna(0)

# 保留成分总和在 85% ~ 105% 的行
valid_df = merged_df[(merged_df["成分总和"] >= 85) & (merged_df["成分总和"] <= 105)]

# 输出最终有效数据
result = valid_df[
    ["文物采样点", "文物编号", "成分总和"] + composition_cols + ["纹饰", "类型", "颜色", "表面风化"]
]

print(result)

# 可选：保存为 Excel 文件
result.to_excel("handled/预处理.xlsx", index=False)

# print(result)
